Pythonモジュールpyodbcでのfetch処理
はじめに
データアナリティクス事業本部のkobayashiです。
前回まで行った環境を使ってPythonモジュールのpyodbcを使ってSQL Serverからデータを取得してRedshiftへ移行する処理を行っていました。その過程で結構なレコード長と件数の入出力を行っていたところ、ふとfetch処理を行う際のメモリ消費量が気になって調べてみましたのでその結果をまとめます。
前回の記事
環境
- macOS Mojave
- Python 3.6.5
- pyodbc 4.0.27
- memory-profiler 0.57.0
pydobcのfectch処理
pyodbcでのfetch処理はfetchall、fetchmany、fetchone、fetchvalがあります。
- fetchall
- クエリのすべての結果レコードを取得する。
- fetchmany
- クエリの結果を指定したレコード数づつ順次取得する。
- fetchone
- クエリの結果を1レコードづつ順次取得する。
- fetchval − クエリの結果の最初の1レコードを取得する。
fetchoneに関してはfetchmanyでも代用出来ますのであまり利用する機会は無いと思います。
The Cursor object represents a database cursor, which is typically used to manage the context of a fetch operation. Database cursors map to ODBC HSTMTs. Cursors created from the same connection are not isolated, i.e. any changes done to the database by one cursor are immediately visible by the other cursors. Note, cursors do not manage database transactions, transactions are committed and rolled-back from the connection.
メモリ消費量計測用のモジュール memory-profilerのインストール
下準備としてまずはPythonスクリプトのメモリ消費量を計測するためのPythonモジュールをインストールします。
This is a python module for monitoring memory consumption of a process as well as line-by-line analysis of memory consumption for python programs. It is a pure python module which depends on the psutil module.
pip install memory-profiler
これは、Pythonスクリプトのメモリ消費量をスクリプトの行ごとに計測できるモジュールで、使い方も非常に簡単で計測する関数にデコレータとして@profileを利用すれば良いだけです。
from memory_profiler import profile @profile def test_func(): ...
メモリ消費量計測
計測用のスクリプト
以下のような非常に簡単なスクリプトで計測します。 単純にデータソースから10万件のレコードを取得し、後続の処理でPandasを使って色々加工する処理を想定しています。
from memory_profiler import profile
import pyodbc
import pandas as pd
import numpy as np
connection = "DRIVER=Redshift;UID=test_user;PWD=test_pass;DATABASE=public;SERVER={hostname};"
@profile
def main():
con = pyodbc.connect(connection)
cur = con.cursor()
query = "select * from test.test_table limit 100000;"
cur.execute(query)
rows = cur.fetchall()
df = pd.DataFrame(np.array(rows))
print(len(df))
cur.close()
@profile
def main2():
con = pyodbc.connect(connection)
cur = con.cursor()
query = "select * from test.test_table limit 100000;"
cur.execute(query)
row_cnt = 1000
rows = cur.fetchmany(row_cnt)
while len(rows) > 0:
df = pd.DataFrame(np.array(rows))
print(len(df))
# next
rows = cur.fetchmany(row_cnt)
cur.close()
if __name__ == "__main__":
main()
main2()
では実際にこのスクリプトを実行してみます。
fetchallを使って一括でデータを取得した場合
Line # Mem usage Increment Line Contents
================================================
19 79.2 MiB 79.2 MiB @profile
20 def main():
21 86.5 MiB 7.3 MiB con = pyodbc.connect(connection)
22
23 86.5 MiB 0.0 MiB cur = con.cursor()
24
25 86.5 MiB 0.0 MiB query = "select * from test.test_table limit 100000;"
26 185.5 MiB 99.0 MiB cur.execute(query)
27
28 399.5 MiB 214.0 MiB rows = cur.fetchall()
29 550.3 MiB 150.8 MiB df = pd.DataFrame(np.array(rows))
30 550.3 MiB 0.0 MiB print(len(df))
31 550.3 MiB 0.0 MiB cur.close()
上記の通り、excecuteを行うまでのメモリ消費量はそれほどでもありませんが、fetchallを行った際にメモリ消費量が大幅に増えています。またPandasデータフレームに変換した際に更に消費量が大幅に増えています。
fetchmanyを使ってレコード数を区切ってデータを取得した場合
Line # Mem usage Increment Line Contents
================================================
34 79.2 MiB 79.2 MiB @profile
35 def main2():
36 86.5 MiB 7.3 MiB con = pyodbc.connect(connection)
37
38 86.5 MiB 0.0 MiB cur = con.cursor()
39
40 86.5 MiB 0.0 MiB query = "select * from test.test_table limit 100000;"
41 185.5 MiB 99.0 MiB cur.execute(query)
42
43 185.5 MiB 0.0 MiB row_cnt = 1000
44 188.1 MiB 2.6 MiB rows = cur.fetchmany(row_cnt)
45 196.2 MiB 0.0 MiB while len(rows) > 0:
46 196.2 MiB 1.4 MiB df = pd.DataFrame(np.array(rows))
47 print(len(df))
48 # next
49 196.2 MiB 2.6 MiB rows = cur.fetchmany(row_cnt)
50
51 156.2 MiB 0.0 MiB cur.close()
上記の通り、excecuteを行うまでのメモリ消費量は処理内容（クエリ）が同一なので当然fetchallと同一です。一方fetchmanyを行った際にはfetchallと比べ大幅に削減されています。これはfetchmanyで取得するレコードを1000件に絞っているためで、その後続でPandasデータフレームに変換した際にもメモリ消費量は大幅に削減されています。
まとめ
レコード長にもよりますが、数千件のレコードでしたら一括で取得して一括でデータを処理してもそれほどリソースも消費しないので特にメモリ消費量のことは考えなくても良いと思います。一方、数十万・数百万件のレコードを取得して加工しようと考えた場合は適切な処理が必要となります。
最後まで読んで頂いてありがとうございました。
